package com.yumo.kangchenjunga.settlement;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public final class StatementService {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public Map<String, Object> queryStatement(int companyId) {
		var result = new HashMap<String, Object>();
		var entry = new HashMap<String, Object>();
		var details = queryDetails(companyId);

		result.put("details", details);

		entry.put("unallocated", details.stream().mapToInt(it -> Integer.valueOf(it.get("unallocated").toString())).sum());
		entry.put("allocated", details.stream().mapToInt(it -> Integer.valueOf(it.get("allocated").toString())).sum());
		entry.put("used", details.stream().mapToInt(it -> Integer.valueOf(it.get("used").toString())).sum());
		entry.put("unused", details.stream().mapToInt(it -> Integer.valueOf(it.get("unused").toString())).sum());
		entry.put("amount", details.stream().map(it -> new BigDecimal((String) it.get("amount")))
				.reduce(BigDecimal.ZERO, (a, b) -> a.add(b)).toPlainString());

		result.put("entry", entry);

		return result;
	}

	private List<Map<String, Object>> queryDetails(int companyId) {
		String sql = String.format("" +
				"  select" +
				"      a.activity_id," +
				"      c.name," +
				"      b.title," +
				"      cast(coalesce(a.unallocated, 0) as char) as unallocated," +
				"      cast(coalesce(a.allocated, 0) as char) as allocated," +
				"      cast(coalesce(a.used, 0) as char) as used," +
				"      cast(coalesce(a.unused, 0) as char) as unused," +
				"      cast(coalesce(round(a.used * b.price, 2), 0) as char) as amount" +
				"    from (" +
				"  select" +
				"      a.activity_id, a.unallocated, a.allocated, b.used, a.unused" +
				"    from (" +
				"  select" +
				"      a.activity_id," +
				"      sum(case when p.status = 'UNALLOCATED' then 1 else 0 end) as unallocated," +
				"      sum(case when l.status = 'ALLOCATED' then 1 else 0 end) as allocated," +
				"      sum(case when c.status = 'OBTAINED' then 1 else 0 end) as unused" +
				"    from coupon_pool p" +
				"      left join coupon_customer_allocation l using (coupon_id)" +
				"      left join coupon_customer_obtainment c using (coupon_id)" +
				"      inner join activity a on a.activity_id = p.activity_id" +
				"    where a.company_id = %d" +
				"    group by a.activity_id) a left join (" +
				"  select" +
				"      activity_id, count(1) as used" +
				"    from coupon_log" +
				"    where company_id = %d and op = 'redeem' and op_result = 'success'" +
				"    group by activity_id) b using (activity_id)) a left join activity b using (activity_id) left join company c using (company_id)" +
				"    order by a.activity_id",
				companyId, companyId);

		return jdbcTemplate.queryForList(sql);
	}

}
